﻿--cuando se registra un consignado pertenece a un grupo de envio
--cada cliente tiene diferentes grupos de envio
CREATE SEQUENCE pca_courier.seq_grupo_consignado_id;
CREATE TABLE pca_courier.grupos_consignado
(
	grupo_consignado_id integer NOT NULL DEFAULT nextval('pca_courier.seq_grupo_consignado_id'),
	cliente_id bigint NOT NULL,
	nombre varchar(150) NOT NULL,
	CONSTRAINT grupos_consignado_pk PRIMARY KEY(grupo_consignado_id),
	CONSTRAINT clientes_fkey FOREIGN KEY(cliente_id)
		REFERENCES pca_clientes.empresas(empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE SEQUENCE pca_courier.seq_consignado_id;
CREATE TABLE pca_courier.consignados
(
	consignado_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_courier.seq_consignado_id')::integer),
	sesion_id bigint NOT NULL,
	correlativo_interno integer NOT NULL DEFAULT currval('pca_courier.seq_consignado_id'),
	correlativo_consignado varchar NOT NULL DEFAULT currval('pca_courier.seq_consignado_id'),
	orden_servicio_id bigint NOT NULL,
	remitente varchar(150),
	orden_servicio_detalle_id bigint NOT NULL,
	pais_id varchar(2) NOT NULL,
	ubigeo_id varchar(10)NOT NULL,
	doc_identidad_id varchar(5),
	num_doc_identidad varchar(20),
	nombre varchar(150) NOT NULL,
	direccion varchar(150) NOT NULL,
	referencia varchar(150),
	empresa varchar(150), -- Nombre de la empresa a la que va dirigida el envio
	cargo varchar(150), --Cargo que ocupa la persona que recive el envio
	telefono varchar(50),
	email varchar(50),
	grupo_consignado_id integer,
	fecha_hora_sistema timestamp with time zone NOT NULL DEFAULT current_timestamp,
	manifiesto_id integer,--el ultimo manifiesto en el que se desapacho
	estado_reparto_id varchar(2) NOT NULL,
	numero_manifiestos integer NOT NULL DEFAULT 0,-- la cantidad de manifiesto emitidos para el consignado
	contador_observaciones integer NOT NULL DEFAULT 0,--la cantidad de observaciones que tiene el consignado
	cambio char(1) NOT NULL,
	CONSTRAINT consignados_pk PRIMARY KEY(consignado_id),
	CONSTRAINT orden_servicio_fkey FOREIGN KEY(orden_servicio_id)
		REFERENCES pca_courier.orden_servicio(orden_servicio_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT sesiones_fkey FOREIGN KEY(sesion_id)
		REFERENCES pca_log.sesiones(sesion_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT orden_servicio_detalle_fkey FOREIGN KEY(orden_servicio_detalle_id)
		REFERENCES pca_courier.orden_servicio_detalle(orden_servicio_detalle_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT ubigeo_fkey FOREIGN KEY (pais_id, ubigeo_id)
		REFERENCES pca_tablas.ubigeo (pais_id, ubigeo_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT docs_indentidad_fkey FOREIGN KEY (doc_identidad_id)
		REFERENCES pca_tablas.docs_identidad (doc_identidad_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT grupos_consignado_fkey FOREIGN KEY(grupo_consignado_id)
		REFERENCES pca_courier.grupos_consignado(grupo_consignado_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT estado_reparto_fkey FOREIGN KEY(estado_reparto_id)
		REFERENCES pca_courier.estado_reparto(estado_reparto_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE SEQUENCE pca_courier.seq_consignado_observacion_id;
CREATE TABLE pca_courier.consignados_observaciones
(
	consignado_observacion_id integer NOT NULL DEFAULT nextval('pca_courier.seq_consignado_observacion_id'),
	consignado_id bigint NOT NULL,
	observaciones text NOT NULL,
	sesion_id bigint NOT NULL,
	fecha_hora_sistema timestamp with time zone NOT NULL DEFAULT current_timestamp,
	activo boolean NOT NULL DEFAULT true,
	CONSTRAINT consignados_observaciones_pk PRIMARY KEY(consignado_observacion_id),
	CONSTRAINT consignados_fkey FOREIGN KEY(consignado_id)
		REFERENCES pca_courier.consignados(consignado_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT sesiones_fkey FOREIGN KEY(sesion_id)
		REFERENCES pca_log.sesiones(sesion_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

-----------------------------------------------------------------------------------------------
-----------------------------------------Triggers----------------------------------------------
-----------------------------------------------------------------------------------------------

--Aumenta-reduce el valor del campo num_consignados del tabla pca_courier.orden_servicio y pca_courier.orden_servicio_detalle
CREATE OR REPLACE FUNCTION pca_courier.fn_contador_consignados() RETURNS trigger AS
$$
BEGIN
	IF(TG_OP='INSERT')THEN
		UPDATE pca_courier.orden_servicio_detalle SET
			numero_consignados=numero_consignados+1
		WHERE
			orden_servicio_detalle_id=NEW.orden_servicio_detalle_id;

		UPDATE pca_courier.orden_servicio SET
			numero_consignados=numero_consignados+1
		WHERE
			orden_servicio_id=NEW.orden_servicio_id;
		
	ELSIF(TG_OP='DELETE')THEN
		UPDATE pca_courier.orden_servicio_detalle SET
			numero_consignados=numero_consignados-1
		WHERE
			orden_servicio_detalle_id=OLD.orden_servicio_detalle_id;

		UPDATE pca_courier.orden_servicio SET
			numero_consignados=numero_consignados-1
		WHERE
			orden_servicio_id=OLD.orden_servicio_id;
	END IF;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tri_contador_consignados ON pca_courier.consignados;
CREATE TRIGGER tri_contador_consignados AFTER INSERT OR DELETE ON pca_courier.consignados
FOR EACH ROW
EXECUTE PROCEDURE pca_courier.fn_contador_consignados();

--Mantiene un conteo de las observaciones de cada consignado
CREATE OR REPLACE FUNCTION pca_courier.fn_contador_consignados_observaciones() RETURNS trigger AS
$$
BEGIN
	IF(TG_OP='INSERT')THEN
		UPDATE pca_courier.consignados SET
			contador_observaciones=contador_observaciones+1
		WHERE
			consignado_id=NEW.consignado_id;
	ELSIF(TG_OP='DELETE')THEN
		UPDATE pca_courier.consignados SET
			contador_observaciones=contador_observaciones-1
		WHERE
			consignado_id=OLD.consignado_id;
	END IF;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tri_contador_consignados_observaciones ON pca_courier.consignados_observaciones;
CREATE TRIGGER tri_contador_consignados_observaciones AFTER INSERT OR DELETE ON pca_courier.consignados_observaciones
FOR EACH ROW
EXECUTE PROCEDURE pca_courier.fn_contador_consignados_observaciones();

-------------------------------------------------------------------------------------------------------
-----------------------------------------------Vistas--------------------------------------------------
-------------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW pca_courier.vw_consignados AS
SELECT
	c.consignado_id,
	c.sesion_id,
	s.usuario_id,
	c.correlativo_interno,
	c.correlativo_consignado,
	c.orden_servicio_id,
	os.serie as serie_orden_servicio,
	os.numero as numero_orden_servicio,
	os.strserienumero AS strserienumero_orden_servicio,
	os.cliente_id,
	initcap(os.cliente)::varchar AS cliente,
	os.fecha_hora_recojo,
	os.tipo_servicio_id,
	ts.nombre as tipo_servicio,
	ts.dias_minimo,
	ts.dias_maximo,
	initcap(c.remitente)::varchar as remitente,
	c.orden_servicio_detalle_id,
	initcap(d.detalle)::varchar AS detalle,
	d.medida,
	d.peso,
	d.tipo_envio,
	c.pais_id,
	c.ubigeo_id,
	u.strubigeo,
	c.doc_identidad_id,
	c.num_doc_identidad,
	initcap(c.nombre)::varchar AS nombre,
	initcap(c.direccion)::varchar AS direccion,
	upper(c.referencia)::varchar AS referencia,
	initcap(c.empresa)::varchar AS empresa,
	initcap(c.cargo)::varchar AS cargo,
	c.telefono,
	lower(c.email)::varchar AS email,
	c.grupo_consignado_id,
	initcap(gc.nombre)::varchar as grupo_consignado,
	c.fecha_hora_sistema,
	c.manifiesto_id,
	c.estado_reparto_id,
	c.numero_manifiestos,
	c.contador_observaciones,
	c.cambio
FROM
	pca_courier.consignados c
INNER JOIN
	pca_log.sesiones s
ON 
	(c.sesion_id=s.sesion_id)
INNER JOIN
	pca_courier.vw_orden_servicio os
ON
	(c.orden_servicio_id=os.orden_servicio_id)
INNER JOIN
	pca_courier.vw_tipo_servicio ts
ON
	(os.tipo_servicio_id=ts.tipo_servicio_id)
INNER JOIN
	pca_courier.vw_orden_servicio_detalle d
ON
	(c.orden_servicio_detalle_id=d.orden_servicio_detalle_id)
INNER JOIN
	pca_tablas.vw_ubigeo u
ON
	(c.pais_id=u.pais_id AND
	c.ubigeo_id=u.ubigeo_id)
LEFT JOIN
	pca_courier.grupos_consignado gc
ON
	(c.grupo_consignado_id=gc.grupo_consignado_id);

CREATE OR REPLACE VIEW pca_courier.vw_consignados_observaciones AS
SELECT
	o.consignado_observacion_id,
	o.consignado_id,
	o.observaciones,
	o.sesion_id,
	s.usuario_id,
	u."Username" AS usuario,
	o.fecha_hora_sistema,
	o.activo
FROM
	pca_courier.consignados_observaciones o
INNER JOIN
	pca_log.sesiones s
ON
	(o.sesion_id=s.sesion_id)
INNER JOIN
	pca_membership."Users" u 
ON 
	(s.usuario_id = u."pId");
	
-------------------------------------------------------------------------------------------------------
-----------------------------Funciones  pca_courier.grupos_consignado----------------------------------
-------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pca_courier.fn_add_grupo_consignado(bigint,--orden_servicio_id $1
							       varchar --nombre $2
							       )RETURNS integer AS
$$
DECLARE
	_cliente_id pca_courier.orden_servicio.cliente_id%TYPE;
	_grupo_consignado_id pca_courier.grupos_consignado.grupo_consignado_id%TYPE;
BEGIN
	SELECT INTO
		_cliente_id cliente_id
	FROM
		pca_courier.orden_servicio
	WHERE
		orden_servicio_id=$1;

	IF(FOUND)THEN
		SELECT INTO
			_grupo_consignado_id grupo_consignado_id
		FROM
			pca_courier.grupos_consignado
		WHERE
			cliente_id = _cliente_id AND
			nombre =$2;
			
		IF (FOUND) THEN
			RETURN _grupo_consignado_id;
		ELSE
			INSERT INTO pca_courier.grupos_consignado
				(cliente_id,
				nombre)
			VALUES
				(_cliente_id,$2);

			RETURN currval('pca_courier.seq_grupo_consignado_id');
		END IF;
	ELSE	
		RAISE EXCEPTION 'No existe orden de servicio con ID : %',$1;
	END IF;
END;
$$
LANGUAGE plpgsql;
							       
CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_grupos_consignado(bigint--cliente_id
								     )RETURNS SETOF pca_courier.grupos_consignado AS
$$
DECLARE
	row_grupo_consignado pca_courier.grupos_consignado%ROWTYPE;
BEGIN
	FOR row_grupo_consignado IN
		SELECT
			grupo_consignado_id,
			cliente_id,
			nombre
		FROM
			pca_courier.grupos_consignado
		WHERE
			cliente_id=$1
		ORDER BY
			nombre
	LOOP
		RETURN NEXT row_grupo_consignado;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------
-------------------------------Funciones pca_courier.consignados---------------------------------------
-------------------------------------------------------------------------------------------------------

--devuelve un valor boolaneo que indica si existe el correlativo en una orden de servicio
CREATE OR REPLACE FUNCTION pca_courier.fn_existe_correlativo_consignado(varchar,--correlativo_consignado $1
									bigint --orden_servicio_id $2
									)RETURNS boolean AS
$$
DECLARE
	_row pca_courier.consignados%ROWTYPE;
BEGIN
	SELECT INTO
		_row
		*
	FROM
		pca_courier.consignados
	WHERE
		correlativo_consignado=$1 AND
		orden_servicio_id=$2;
	IF(FOUND)THEN
		RETURN true;
	ELSE
		RETURN false;
	END IF;
END
$$
LANGUAGE plpgsql;

--devuelve el maximo correlativo + 1 para una orden de servicio
CREATE OR REPLACE FUNCTION pca_courier.fn_get_max_correlativo_consignado(bigint--orden_servicio_id $1
									 ) RETURNS varchar AS
$$
DECLARE
	_correlativo_consignado integer;
BEGIN
	SELECT INTO 
		_correlativo_consignado
		max(correlativo_consignado::integer)+1
	FROM 
		pca_courier.consignados
	WHERE 
		orden_servicio_id=$1;

	IF(_correlativo_consignado ISNULL) THEN
		_correlativo_consignado := 1;
	END IF;

	RETURN _correlativo_consignado::varchar;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_add_consignado(bigint,--sesion_id $1
							 varchar,--correlativo_consignado $2
							 bigint,--orden_servicio_id $3
							 varchar,--remitente $4
							 bigint,--orden_servicio_detalle_id $5
							 varchar,--pais_id $6
							 varchar,--ubigeo_id $7
							 varchar,--doc_identidad_id $8
							 varchar,--num_doc_identidad $9
							 varchar,--nombre $10
							 varchar,--direccion $11
							 varchar,--referencia $12
							 varchar,--empresa $13
							 varchar,--cargo $14
							 varchar,--telefono $15
							 varchar,--email $16
							 varchar --grupo_consignado $17
							) RETURNS bigint AS
$$
DECLARE
	_row pca_courier.orden_servicio%ROWTYPE;
	_grupo_consignado_id pca_courier.consignados.grupo_consignado_id%TYPE;
	_existe_correlativo_consignado boolean;
BEGIN
	SELECT INTO 
		_row 
		* 
	FROM 
		pca_courier.orden_servicio 
	WHERE 
		orden_servicio_id=$3;

	IF(_row.estado_orden_servicio_id='00') THEN	

		--validamos que el correlativo sea numero si es que la configuracion de la orden de servicio lo indica
		IF (_row.correlativo_consignado_numerico) THEN
			IF($2 NOTNULL)THEN
				IF(NOT pca_global.fn_isinteger_positive($2))THEN
					RAISE EXCEPTION 'La configuracion de la orden de servicio solo permite valores numericos, correlativo = %',$2;
				END IF;
			ELSE --asignamos correlativo autonumerico al consignado
				$2 := pca_courier.fn_get_max_correlativo_consignado($3);
			END IF;
		END IF;

		--validamos que el correlativo sea unico si la configuracion de la orden de servicio lo indica
		IF(_row.correlativo_consignado_unq)THEN
			_existe_correlativo_consignado := pca_courier.fn_existe_correlativo_consignado($2,$3);

			IF(_existe_correlativo_consignado)THEN
				RAISE EXCEPTION 'Ya existe un consignado con el correlativo %',$2;
			END IF;
		END IF;
	
		_grupo_consignado_id := NULL;

		IF($17 NOTNULL)THEN
			_grupo_consignado_id := pca_courier.fn_add_grupo_consignado($3,$17);
		END IF;

		INSERT INTO pca_courier.consignados
			(sesion_id,
			correlativo_consignado,
			orden_servicio_id,
			remitente,
			orden_servicio_detalle_id,
			pais_id,
			ubigeo_id,
			doc_identidad_id,
			num_doc_identidad,
			nombre,
			direccion,
			referencia,
			empresa,
			cargo,
			telefono,
			email,
			grupo_consignado_id,
			estado_reparto_id,
			cambio)
		VALUES
			($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,_grupo_consignado_id,'00','I');

		RETURN pca_global.fn_pseudo_encrypt_bigint(currval('pca_courier.seq_consignado_id')::integer);
	ELSE
		RAISE EXCEPTION 'Orden de servicio no esta en RECEPCION';
	END IF;	
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_add_consignado(bigint,--sesion_id $1
							 varchar,--correlativo_consignado $2
							 bigint,--orden_servicio_id $3
							 varchar,--remitente $4
							 varchar,--orden_servicio_detalle $5
							 varchar,--pais_id $6
							 varchar,--ubigeo_id $7
							 varchar,--doc_identidad_id $8
							 varchar,--num_doc_identidad $9
							 varchar,--nombre $10
							 varchar,--direccion $11
							 varchar,--referencia $12
							 varchar,--empresa $13
							 varchar,--cargo $14
							 varchar,--telefono $15
							 varchar,--email $16
							 varchar --grupo_consignado $17
							) RETURNS bigint AS
$$
DECLARE
	_estado_orden_servicio_id pca_courier.orden_servicio.estado_orden_servicio_id%TYPE;
	_orden_servicio_detalle_id pca_courier.consignados.orden_servicio_detalle_id%TYPE;
BEGIN
	SELECT INTO 
		_estado_orden_servicio_id estado_orden_servicio_id 
	FROM 
		pca_courier.orden_servicio 
	WHERE 
		orden_servicio_id=$3;

	IF(_estado_orden_servicio_id='00') THEN	
		_orden_servicio_detalle_id= pca_courier.fn_add_orden_servicio_detalle($3,$5,'01',0.1,'00');

		RETURN pca_courier.fn_add_consignado($1,$2,$3,$4,_orden_servicio_detalle_id,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17);
	ELSE
		RAISE EXCEPTION 'Orden de servicio no esta en RECEPCION';
	END IF;	
END;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION pca_courier.fn_update_consignado(bigint,--consignado_id $1
							    bigint,--sesion_id $2
							    varchar,--correlativo_consignado $3
							    bigint,--orden_servicio_id $4
							    varchar,--remitente $5
							    bigint,--orden_servicio_detalle_id $6
							    varchar,--pais_id $7
							    varchar,--ubigeo_id $8
							    varchar,--doc_identidad_id $9
							    varchar,--num_doc_identidad $10
							    varchar,--nombre $11
							    varchar,--direccion $12
							    varchar,--referencia $13
							    varchar,--empresa $14
							    varchar,--cargo $15
							    varchar,--telefono $16
							    varchar,--email $17
							    varchar --grupo_consignado $18
							    ) RETURNS void AS
$$
DECLARE
	_row_orden_servicio pca_courier.orden_servicio%ROWTYPE;
	_grupo_consignado_id pca_courier.consignados.grupo_consignado_id%TYPE;
	_row_consignado pca_courier.consignados%ROWTYPE;
	_existe_correlativo_consignado boolean;
BEGIN
	SELECT INTO 
		_row_orden_servicio
		*
	FROM 
		pca_courier.orden_servicio 
	WHERE 
		orden_servicio_id=$4;

	IF(_row_orden_servicio.estado_orden_servicio_id='00') THEN	

		SELECT INTO
			_row_consignado
			*
		FROM
			pca_courier.consignados
		WHERE
			consignado_id=$1;

		--IF(_row_consignado.estado_reparto_id='00')THEN

			--validamos que el correlativo sea numero si es que la configuracion de la orden de servicio lo indica
			IF (_row_orden_servicio.correlativo_consignado_numerico) THEN
				IF(NOT pca_global.fn_isinteger_positive($3))THEN
					RAISE EXCEPTION 'La configuracion de la orden de servicio solo permite valores numericos, correlativo = %',$2;
				END IF;
			END IF;

			--validamos que el correlativo sea unico si la configuracion de la orden de servicio lo indica
			IF(_row_orden_servicio.correlativo_consignado_unq AND (_row_consignado.correlativo_consignado <> $3))THEN
				_existe_correlativo_consignado := pca_courier.fn_existe_correlativo_consignado($3,$4);

				IF(_existe_correlativo_consignado)THEN
					RAISE EXCEPTION 'Ya existeun consignado con el correlativo %',$2;
				END IF;
			END IF;
		
			_grupo_consignado_id := NULL;
		
			IF($18 NOTNULL)THEN
				_grupo_consignado_id := pca_courier.fn_add_grupo_consignado($4,$18);
			END IF;

			UPDATE pca_courier.consignados SET
				sesion_id=$2,
				correlativo_consignado=$3,
				orden_servicio_id=$4,
				remitente=$5,
				orden_servicio_detalle_id=$6,
				pais_id=$7,
				ubigeo_id=$8,
				doc_identidad_id=$9,
				num_doc_identidad=$10,
				nombre=$11,
				direccion=$12,
				referencia=$13,
				empresa=$14,
				cargo=$15,
				telefono=$16,
				email=$17,
				grupo_consignado_id=_grupo_consignado_id,
				cambio = 'A'
			WHERE
				consignado_id=$1;
		--ELSE
		--	RAISE EXCEPTION 'Consignado no esta en RECEPCION';
		--END IF;
	ELSE
		RAISE EXCEPTION 'Orden de servicio no esta en RECEPCION';
	END IF;	
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_update_consignado(bigint,--consignado_id $1
							    bigint,--sesion_id $2
							    varchar,--correlativo_consignado $3
							    bigint,--orden_servicio_id $4
							    varchar,--remitente $5
							    varchar,--orden_servicio_detalle $6
							    varchar,--pais_id $7
							    varchar,--ubigeo_id $8
							    varchar,--doc_identidad_id $9
							    varchar,--num_doc_identidad $10
							    varchar,--nombre $11
							    varchar,--direccion $12
							    varchar,--referencia $13
							    varchar,--empresa $14
							    varchar,--cargo $15
							    varchar,--telefono $16
							    varchar,--email $17
							    varchar --grupo_consignado $18
							    ) RETURNS void AS
$$
DECLARE
	_estado_orden_servicio_id pca_courier.orden_servicio.estado_orden_servicio_id%TYPE;
	_orden_servicio_detalle_id pca_courier.consignados.orden_servicio_detalle_id%TYPE;
BEGIN
	SELECT INTO 
		_estado_orden_servicio_id estado_orden_servicio_id 
	FROM 
		pca_courier.orden_servicio 
	WHERE 
		orden_servicio_id=$3;

	IF(_estado_orden_servicio_id='00') THEN	
		_orden_servicio_detalle_id := pca_courier.fn_add_orden_servicio_detalle($4,$6,'01',0.1,'00');

		EXECUTE pca_courier.fn_update_consignado($1,$2,$3,$4,$5,_orden_servicio_detalle_id,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18);
	ELSE
		RAISE EXCEPTION 'Orden de servicio no esta en RECEPCION';
	END IF;	
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_del_consignado(bigint,--consignado_id $1
							 boolean --true para eliminar permanentemente, false para marcar para eliminar $2
							 ) RETURNS void AS
$$
DECLARE
	_estado_orden_servicio_id pca_courier.orden_servicio.estado_orden_servicio_id%TYPE;
	_orden_servicio_id pca_courier.consignados.orden_servicio_id%TYPE;
BEGIN
	SELECT INTO
		_orden_servicio_id orden_servicio_id
	FROM 
		pca_courier.consignados
	WHERE
		consignado_id=$1;

	IF(FOUND)THEN
		SELECT INTO 
			_estado_orden_servicio_id estado_orden_servicio_id 
		FROM 
			pca_courier.orden_servicio 
		WHERE 
			orden_servicio_id=_orden_servicio_id;

		IF(_estado_orden_servicio_id='00') THEN
			IF($2)THEN
				DELETE FROM 
					pca_courier.consignados
				WHERE
					consignado_id = $1;
			ELSE
				UPDATE pca_courier.consignados SET
					cambio = 'E'
				WHERE
					consignado_id = $1;
			END IF;
		ELSE
			RAISE EXCEPTION 'Orden de servicio no esta en RECEPCION';
		END IF;
	ELSE
		RAISE EXCEPTION 'No existe consignado o ya fue eliminado';
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_consignado(bigint,--consignado_id $1
							 integer --zona horaria existente en la tabla pca_tablas.zonas $2
							 )RETURNS pca_courier.vw_consignados AS
$$
DECLARE
	_row pca_courier.vw_consignados%ROWTYPE;
	_zona_horaria varchar; --la zona horaria a usar
BEGIN
	SELECT INTO _zona_horaria nombre FROM pca_tablas.zonas WHERE zona_id=$2;

	IF(FOUND)THEN
		execute  'SET TIMEZONE TO ''' || _zona_horaria || '''';
		
		SELECT
			consignado_id,
			sesion_id,
			usuario_id,
			correlativo_interno,
			correlativo_consignado,
			orden_servicio_id,
			serie_orden_servicio,
			numero_orden_servicio,
			strserienumero_orden_servicio,
			cliente_id,
			cliente,
			fecha_hora_recojo,
			tipo_servicio_id,
			tipo_servicio,
			dias_minimo,
			dias_maximo,
			remitente,
			orden_servicio_detalle_id,
			detalle,
			medida,
			peso,
			tipo_envio,
			pais_id,
			ubigeo_id,
			strubigeo,
			doc_identidad_id,
			num_doc_identidad,
			nombre,
			direccion,
			referencia,
			empresa,
			cargo,
			telefono,
			email,
			grupo_consignado_id,
			grupo_consignado,
			fecha_hora_sistema,
			manifiesto_id,
			estado_reparto_id,
			numero_manifiestos,
			contador_observaciones,
			cambio
		INTO
			_row
		FROM
			pca_courier.vw_consignados
		WHERE
			consignado_id=$1;
			
		RETURN _row;
	ELSE
		RAISE EXCEPTION 'No existe zona horaria con id %',$2;
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_resumen_estado_reparto(bigint --orden_servicio_id $1
								     )RETURNS SETOF RECORD AS
$$
DECLARE
	_resumen RECORD;
	_resul RECORD;
	count_recepcion bigint;
	count_factory bigint;
	count_clasificacion bigint;
	count_transito bigint;
	count_finalizado bigint;
BEGIN
	count_recepcion :=0;
	count_factory :=0;
	count_clasificacion :=0;
	count_transito :=0;
	count_finalizado :=0;

	FOR _resumen IN
		SELECT 
			estado_reparto_id,count(estado_reparto_id) as total 
		FROM 
			pca_courier.consignados 
		WHERE
			orden_servicio_id=$1
		GROUP BY 
			estado_reparto_id
	LOOP
		IF(_resumen.estado_reparto_id='00')THEN
			count_recepcion=_resumen.total;
		ELSIF(_resumen.estado_reparto_id='01')THEN
			count_factory=_resumen.total;
		ELSIF(_resumen.estado_reparto_id='02')THEN
			count_clasificacion=_resumen.total;
		ELSIF(_resumen.estado_reparto_id='03')THEN
			count_transito=_resumen.total;
		ELSIF(_resumen.estado_reparto_id='04')THEN
			count_finalizado=_resumen.total;
		END IF;
	END LOOP;

	FOR _resul IN
		SELECT
			count_recepcion,
			count_factory,
			count_clasificacion,
			count_transito,
			count_finalizado
	LOOP
		RETURN NEXT _resul;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_sendtoclasificacion_consignado(bigint, --orden_servicio_id $1
								         bigint --consignado_id $2
								         )RETURNS void AS
$$
DECLARE
	_estado_orden_servicio_id pca_courier.orden_servicio.estado_orden_servicio_id%TYPE;
	_row_consignado RECORD;
	_ValOrdenForSendToAreaClasificacion boolean;
BEGIN
	_ValOrdenForSendToAreaClasificacion := pca_courier.fn_get_config('ValOrdenForSendToAreaClasificacion')::boolean;
	
	IF (_ValOrdenForSendToAreaClasificacion) THEN
		_estado_orden_servicio_id := pca_courier.fn_get_orden_servicio_estado($1);
		IF(_estado_orden_servicio_id<>'01')THEN
			RAISE EXCEPTION 'La orden de servicio N° % ,no esta en OPERACIONES estado actual = %',pca_courier.fn_get_strserienumero_orden_servicio($1),
												              pca_courier.fn_get_nombre_estado_orden_servicio(_estado_orden_servicio_id);
		END IF;
	END IF;

	SELECT INTO
		_row_consignado
		orden_servicio_id,
		estado_reparto_id
	FROM
		pca_courier.consignados
	WHERE
		consignado_id=$2;

	IF(FOUND)THEN
		IF(_row_consignado.orden_servicio_id=$1)THEN
			IF(_row_consignado.estado_reparto_id='01')THEN
				UPDATE 
					pca_courier.consignados
				SET
					estado_reparto_id='02'
				WHERE
					consignado_id=$2;
			ELSE
				RAISE EXCEPTION 'El consignado no se encuentra en el area de factory, no se puede enviar a CLASIFICACION, estado actual = %',pca_courier.fn_get_nombre_estado_reparto(_row_consignado.estado_reparto_id);
			END IF;
		ELSE
			RAISE EXCEPTION 'El consignado con ID=% no pertenece a la orden de servicio N° %',$2,pca_courier.fn_get_strserienumero_orden_servicio($1);
		END IF;
	ELSE
		RAISE EXCEPTION 'No existe consignado con ID=%',$2;
	END IF;
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------
----------------------------Funciones pca_courier.consignados_observaciones----------------------------
-------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pca_courier.fn_add_consignado_observacion(bigint,--consignado_id $1
								     text,--observaciones $2
								     bigint --sesion_id $3
								     )RETURNS integer AS
$$
BEGIN
	INSERT INTO pca_courier.consignados_observaciones
		(consignado_id,
		observaciones,
		sesion_id)
		VALUES
		($1,$2,$3);
		
	RETURN currval('pca_courier.seq_consignado_observacion_id');
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_update_consignado_observacion(integer,--consignado_observacion_id $1
									bigint,--consignado_id $2
									text,--observaciones $3
									bigint --sesion_id $4
									)RETURNS void AS
$$
BEGIN
	UPDATE pca_courier.consignados_observaciones SET
		consignado_id=$2,
		observaciones=$3,
		sesion_id=$4
	WHERE
		consignado_observacion_id=$1;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_update_estado_consignado_observacion(integer,--consignado_observacion_id $1
									       boolean --activo $2
									       )RETURNS void AS
$$
BEGIN
	UPDATE pca_courier.consignados_observaciones SET
		activo=$2
	WHERE
		consignado_observacion_id=$1;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_consignado_observacion(integer--consignado_observacion_id $1
								     )RETURNS SETOF pca_courier.vw_consignados_observaciones AS
$$
DECLARE
	_row pca_courier.vw_consignados_observaciones%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			consignado_observacion_id,
			consignado_id,
			observaciones,
			sesion_id,
			usuario_id,
			usuario,
			fecha_hora_sistema,
			activo
		FROM
			pca_courier.vw_consignados_observaciones
		WHERE
			consignado_observacion_id=$1
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_consignado_observacion(bigint--consignado_id $1
									  )RETURNS SETOF pca_courier.vw_consignados_observaciones AS
$$
DECLARE
	_row pca_courier.vw_consignados_observaciones%ROWTYPE;
BEGIN
	FOR _row IN
		SELECT
			consignado_observacion_id,
			consignado_id,
			observaciones,
			sesion_id,
			usuario_id,
			usuario,
			fecha_hora_sistema,
			activo
		FROM
			pca_courier.vw_consignados_observaciones
		WHERE
			consignado_id=$1
		ORDER BY
			consignado_observacion_id ASC
	LOOP
		RETURN NEXT _row;
	END LOOP;
END;
$$
LANGUAGE plpgsql;